﻿Public Class clsGtProcedureDetails

    Public Function getGtProcedureDetailsList(b_gt_sys_id As String, procedure_no As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_procedure_details "
        szSql &= "where b_gt_sys_id = ? and validity = ? and procedure_no = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1", procedure_no)
        If IsNothing(dt) Then
            getGtProcedureDetailsList = New DataTable
        Else
            getGtProcedureDetailsList = dt
        End If
    End Function

    Public Function getGtProcedureDetailsList(b_gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_procedure_details "
        szSql &= "where b_gt_sys_id = ? and validity = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1")
        If IsNothing(dt) Then
            getGtProcedureDetailsList = New DataTable
        Else
            getGtProcedureDetailsList = dt
        End If
    End Function

    Public Function save(en As entity_gt_procedure_details) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= "INSERT INTO sf.b_gt_procedure_details(procedure_no,oper_num,b_gt_sys_id,validity,operator,update_time) "
            sql &= "VALUES (?,?,?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.oper_num, en.b_gt_sys_id, en.validity, en.oper)
            sql = ""
            sql &= " select * from sf.b_gt_procedure where b_gt_sys_id=? and procedure_no=? "
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.procedure_No)
            If dt.Rows.Count = 1 Then
                sql = ""
                sql &= "UPDATE sf.b_gt_procedure "
                sql &= " SET oper_num = oper_num + ?, update_time=(SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE b_gt_sys_id = ? and procedure_no = ? "
                DBacs.ExecuteUpdateSql(sql, -1, en.oper_num, en.b_gt_sys_id, en.procedure_No)
            End If
            If dt.Rows.Count = 0 Then
                sql = ""
                sql &= "INSERT INTO sf.b_gt_procedure(procedure_no,oper_num,b_gt_sys_id,update_time) "
                sql &= "VALUES (?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.oper_num, en.b_gt_sys_id)
            End If

            sql = ""
            sql &= " select * from sf.b_gt_result_details where b_gt_sys_id=? and procedure_no=? "
            Dim dtRusult As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.procedure_No)
            If dtRusult.Rows.Count = 1 Then
                sql = ""
                sql &= "UPDATE sf.b_gt_result_details SET "
                sql &= " ok_num = ? - ng_num, update_time=(SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE b_gt_sys_id = ? and procedure_no = ? "
                If dt.Rows.Count = 1 Then
                    Dim op_num = Integer.Parse(dt.Rows(0)("oper_num").ToString()) + en.oper_num
                    DBacs.ExecuteUpdateSql(sql, -1, op_num, en.b_gt_sys_id, en.procedure_No)
                End If
                If dt.Rows.Count = 0 Then
                    DBacs.ExecuteUpdateSql(sql, -1, en.oper_num, en.b_gt_sys_id, en.procedure_No)
                End If
            End If
            If dtRusult.Rows.Count = 0 Then
                sql = ""
                sql &= "INSERT INTO sf.b_gt_result_details(procedure_no,ok_num,b_gt_sys_id,update_time) "
                sql &= "VALUES (?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.oper_num, en.b_gt_sys_id)
            End If
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updateValidity(sys_id As String, oper_num As String, b_gt_sys_id As String, procedure_no As String, oper As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            Dim integer_procedure_No As Integer = Integer.Parse(procedure_no)

            sql &= "UPDATE sf.b_gt_procedure_details"
            sql &= " SET validity = ?, operator_validity = ?, update_time_validity = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "0", oper, sys_id)

            sql = ""
            sql &= "UPDATE sf.b_gt_procedure"
            sql &= " SET oper_num = oper_num - ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE procedure_no = ? and b_gt_sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, oper_num, procedure_no, b_gt_sys_id)

            sql = ""
            sql &= " select * from sf.b_gt_procedure where b_gt_sys_id=? and procedure_no=? "
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, b_gt_sys_id, procedure_no)

            sql = ""
            sql &= "UPDATE sf.b_gt_result_details "
            sql &= " SET ok_num = ? - ng_num, update_time=(SELECT CURRENT_TIMESTAMP(6))"
            sql &= " WHERE b_gt_sys_id = ? and procedure_no = ? "
            Dim op_num = Integer.Parse(dt.Rows(0)("oper_num").ToString())
            DBacs.ExecuteUpdateSql(sql, -1, op_num, b_gt_sys_id, procedure_no)

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

End Class
